﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOQLDV;
using System.Data;
using System.Data.OleDb;

namespace DAOQLDV
{
    public class DAODuong
    {
        public static List<DTODuong> LayDanhSachDuong()
        {
            List<DTODuong> dsDuong = new List<DTODuong>();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From Duong";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                while (dr.Read())
                {
                    DTODuong dtoD = new DTODuong();
                    dtoD.MaDuong = (int)dr["MaDuong"];
                    dtoD.TenDuong = (String)dr["TenDuong"];
                    dsDuong.Add(dtoD);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dsDuong;
        }
        public static DTODuong LayThongTinDuong(String tenDuong)
        {
            DTODuong dtoDuong = new DTODuong();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From Duong Where TenDuong Like '"+tenDuong+"'";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                if (dr.Read())
                {
                    dtoDuong.MaDuong = (int)dr["MaDuong"];
                    dtoDuong.TenDuong = dr["TenDuong"].ToString();
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dtoDuong;
        }        
        public static bool ThemDuong(DTODuong dtoD)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoD.TenDuong.Equals(""))
                {
                    da.Open();
                    da.BeginTran();
                    String sql = "Insert Into TenDuong(TenDuong) Values ('" + dtoD.TenDuong + "')";
                    da.ExecuteNonQuery(sql);
                    sql = "Select @@IDENTITY";
                    dtoD.MaDuong = (int)da.ExecuteScalar(sql);
                    DAOTuKhoaDuong.ThemTuKhoaDuong(dtoD,da);
                    da.CommitTran();
                    kq = true;
                }
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool CapNhatDuong(DTODuong dtoD)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoD.TenDuong.Equals(""))
                {
                    da.Open();
                    String sql = "Update TenDuong Set TenDuong = '" + dtoD.TenDuong + "' Where MaTenDuong = " + dtoD.MaDuong;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool XoaDuong(DTODuong dtoD)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoD.TenDuong.Equals(""))
                {
                    da.Open();
                    String sql = "Delete From TenDuong Where MaTenDuong = " + dtoD.MaDuong;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static int LayMaDuong(String tenDuong)
        {
            int kq = 0;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select MaDuong From Duong Where TenDuong Like '" + tenDuong + "'";
                kq = int.Parse("0" + da.ExecuteScalar(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
    }
}
